﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using System.Text;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Web;
using System.Threading;
using System.Data.SqlClient;
using System.Collections;

namespace XMK.Tadong.StructuredData_IntoDb.Data
{
    /// <summary>
    /// 20060207
    /// Add By Joker
    /// 数据操作公共层
    /// 提供一些存取数据库的接口函数，保持一个数据库链接。
    /// </summary>
    public sealed class OraDataService
    {
        /// <summary>
        /// 数据库连接层
        /// </summary>
        private OracleConnection oraConnection;

        /// <summary>
        /// ora的连接方式
        /// </summary>
        public string szOraConn;
        /// <summary>
        /// ole的连接方式
        /// </summary>
        public string szOleConn;

        private int iCurRowsNum = 0;                    //当前行数，在实务更新表 行更新前事件中使用
        private bool IsIgnoreKeyConflict = true;        //是否忽略主键冲突，如果主键冲突则跳过当前行，保留以前的数据

        public OraDataService(String strConn)
        {

            this.szOraConn = strConn;
            this.oraConnection = new OracleConnection(this.szOraConn);

        }

        public OraDataService()
        {
            //ConnectionStringSettingsCollection connectionStrings = ConfigurationManager.ConnectionStrings;

            //this.szOraConn = connectionStrings["OraConString"].ToString();
            //this.oraConnection = new OracleConnection(this.szOraConn);
            //this.szOleConn = connectionStrings["OleConString"].ToString();
            this.szOraConn = ConfigurationSettings.AppSettings["ConnectionString"];
            this.oraConnection = new OracleConnection(this.szOraConn);
            this.szOleConn = ConfigurationSettings.AppSettings["ConnectionString"];

        }

        #region 利用默认的连接串OraConString 和 数据库组件oracleclient操作数据库
        /// <summary>
        /// 返回当前数据库连接
        /// </summary>
        /// <returns></returns>
        public OracleConnection GetOraConn()
        {
            if (this.oraConnection.State == ConnectionState.Closed)
            {
                this.oraConnection.Open();
                return this.oraConnection;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 执行SQL查询，返回结果的第一行第一列
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns>查询结果</returns>
        public object GetValue(string sql)
        {
            OracleCommand OraCmd = new OracleCommand(sql, this.oraConnection);
            OraCmd.CommandType = CommandType.Text;

            try
            {
                this.oraConnection.Open();
                object o = OraCmd.ExecuteScalar();
                this.oraConnection.Close();
                return o;
            }
            catch (Exception er)
            {
                //LogErrInfo.WriteErrInfo("数据访问层.txt",er.ToString());	
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>					
        public OracleDataReader GetDataReader(string sql)
        {
            OracleCommand OraCmd = new OracleCommand(sql, this.oraConnection);
            OraCmd.CommandType = CommandType.Text;

            try
            {
                this.oraConnection.Open();
                return OraCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }

        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataSet GetDataset(string sql)
        {
            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, this.oraConnection);
            DataSet myDataSet = new DataSet();

            try
            {
                if (this.oraConnection.State == ConnectionState.Closed)
                    this.oraConnection.Open();

                OraAdapter.Fill(myDataSet);
                return myDataSet;
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据表
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataTable GetTable(string sql)
        {   
            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, this.oraConnection);
            DataSet myDataSet = new DataSet();
            try
            {
                this.oraConnection.Open();
                OraAdapter.Fill(myDataSet);
                //  JrscSoft.WellSystem.AppLog.LogInfo(sql);


                return myDataSet.Tables[0];
            }
            catch (Exception er)
            {
                // JrscSoft.WellSystem.AppLog.LogError(er.ToString() + @"\n\t" + sql);

                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据表，代分页效果，只显示当前页数的记录数
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="nPage">当前页指针</param>
        /// <param name="nPageSize">页面记录行数量</param>
        /// <param name="nRecsCount">总记录数</param>
        /// <param name="nEndRecs">当前页终止记录行</param>
        /// <param name="nStartRecs">当前页起始记录行行</param>
        /// <returns></returns>	
        public DataTable GetTable(string sql, int nPage, int nPageSize, out int nRecsCount, out int nStartRecs, out int nEndRecs)
        {
            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, this.oraConnection);
            DataSet myDataSet = new DataSet();

            try
            {
                //得到当前sql语句总记录行
                string strSqlCount = "Select Count(*) From ( " + sql + ")";
                object o = this.GetValue(strSqlCount);
                if (o != null)
                {
                    nRecsCount = Convert.ToInt32(o);
                }
                else
                {
                    nRecsCount = 0;
                }
                //当前页的起始行		
                nStartRecs = (nPage - 1) * nPageSize + 1;
                //当前页的终止行，如果计算操作范围则以当前表最大记录数为主
                nEndRecs = nPage * nPageSize;
                if (nEndRecs > nRecsCount)
                {
                    nEndRecs = nRecsCount;
                }

                this.oraConnection.Open();
                OraAdapter.Fill(myDataSet, nStartRecs - 1, nPageSize, "Table1");
                return myDataSet.Tables["Table1"];

            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="nPage"></param>
        /// <param name="nPageSize"></param>
        /// <param name="nRecsCount"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql, int nPage, int nPageSize, out int nRecsCount, out int nPageCount)
        {
            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, this.oraConnection);
            DataSet myDataSet = new DataSet();

            try
            {
                //得到当前sql语句总记录行
                string strSqlCount = "Select Count(*) From ( " + sql + " )";
                object o = this.GetValue(strSqlCount);
                if (o != null)
                {
                    nRecsCount = Convert.ToInt32(o);
                }
                else
                {
                    nRecsCount = 0;
                }
                //当前页的起始行		
                int nStartRecs = (nPage - 1) * nPageSize + 1;
                //当前页的终止行，如果计算操作范围则以当前表最大记录数为主
                int nEndRecs = nPage * nPageSize;
                if (nEndRecs > nRecsCount)
                {
                    nEndRecs = nRecsCount;
                }
                //总的页数记录
                if ((nRecsCount % nPageSize) == 0)
                {
                    nPageCount = nRecsCount / nPageSize;
                }
                else
                {
                    nPageCount = nRecsCount / nPageSize + 1;
                }

                this.oraConnection.Open();
                OraAdapter.Fill(myDataSet, nStartRecs - 1, nPageSize, "Table1");
                return myDataSet.Tables["Table1"];

            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }


        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ora连接字符串</param>
        /// <returns>成功即返回真，否则就返回假</returns>
        public bool ExeSql(string strSql)
        {
            bool bSucceeded = false;

            OracleConnection myConnection = new OracleConnection(this.szOraConn);
            myConnection.Open();

            OracleCommand myCommand = myConnection.CreateCommand();
            OracleTransaction myTrans;

            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            myCommand.Transaction = myTrans;

            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                bSucceeded = true;
            }
            catch (Exception er)
            {
                myTrans.Rollback();

                Debug.WriteLine(er.ToString() + ",异常在：" + strSql);
                throw er;
            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }


        #region 数据适配器 20060620 Add By Joker
        /// <summary>
        /// 根据传入的Sql语句得到数据adapter
        /// </summary>
        /// <param name="sql">select sql </param>
        /// <returns></returns>
        public System.Data.OracleClient.OracleDataAdapter GetAdapter(string strSql)
        {

            OracleCommand cmd = this.oraConnection.CreateCommand();

            cmd.CommandText = strSql;

            cmd.CommandType = CommandType.Text;

            OracleDataAdapter OraAdapter = new OracleDataAdapter(cmd);

            OracleCommandBuilder cb = new OracleCommandBuilder(OraAdapter);

            OraAdapter.DeleteCommand = cb.GetDeleteCommand();
            OraAdapter.UpdateCommand = cb.GetUpdateCommand();
            OraAdapter.InsertCommand = cb.GetInsertCommand();

            return OraAdapter;
        }

        #endregion

        /// <summary>
        /// 下载数据
        /// </summary>
        /// <param name="szUserCode"></param>
        /// <param name="szFeedbackDate"></param>
        /// <param name="szField"></param>
        /// <param name="szSql"></param>
        /// <param name="szFilaPath"></param>
        /// <param name="szFilter"></param>
        public bool DownloadData(string szSql, string szFilePath)
        {
            bool bSeced = false;
            try
            {
                FileStream fstr = new FileStream(szFilePath, FileMode.OpenOrCreate, FileAccess.Write);

                //生成文件
                OracleCommand ocmd = new OracleCommand(szSql, this.oraConnection);
                if (this.oraConnection.State == ConnectionState.Closed)
                    this.oraConnection.Open();

                OracleDataReader odr = ocmd.ExecuteReader();
                OracleLob olob = OracleLob.Null;
                byte[] buffer = new byte[1024000];
                odr.Read();
                //获得第一个字段的值
                olob = odr.GetOracleLob(0);

                int countRead = olob.Read(buffer, 0, buffer.Length);
                while (countRead > 0)
                {
                    fstr.Write(buffer, 0, countRead);
                    countRead = olob.Read(buffer, 0, buffer.Length);
                }
                olob.Close();
                odr.Close();
                fstr.Close();

                bSeced = true;
            }
            catch (Exception er)
            {
                //JrscSoft.WellSystem.AppLog.LogError( "下载数据：" + er.ToString() );
                Debug.WriteLine(er.ToString() + ",异常在：" + szSql);
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
            return bSeced;
        }

        #endregion

        #region 利用默认的连接串oleConString 和 数据库组件oleConn操作数据库
        /// <summary>
        /// 根据给定的连接字符串执行sql语句来返回具有数据的只读向前的Reader
        /// </summary>
        /// <param name="strSql">提取数据的sql语句</param>
        /// <param name="strCon">数据库连接字符串</param>
        /// <returns>返回含有数据的reader</returns>
        private OleDbDataReader getOleDataReader(string strSql)
        {
            OleDbDataReader reader = null;
            try
            {
                OleDbConnection con = new OleDbConnection(this.szOleConn);

                con.Open();

                OleDbCommand cmd = new OleDbCommand(strSql, con);

                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception er)
            {
                //Debug.WriteLine(er.ToString());
                //LogErrInfo.WriteErrInfo("数据访问层.txt",er.ToString());;	
                //throw er;
                Debug.WriteLine(er.ToString() + ",异常在：" + strSql);
                throw er;
            }

            return reader;
        }


        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ole连接字符串</param>
        /// <returns>成功就返回真，否则就返回假</returns>
        private bool ExecOleSql(string strSql)
        {
            bool bSucceeded = false;
            OleDbConnection myConnection = new OleDbConnection(this.szOleConn);
            myConnection.Open();

            OleDbCommand myCommand = myConnection.CreateCommand();
            OleDbTransaction myTrans;

            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;

            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                bSucceeded = true;
            }
            catch (Exception e)
            {

                //Debug.WriteLine(e.ToString());
                //LogErrInfo.WriteErrInfo("数据访问层.txt",e.ToString());;	

                try
                {
                    myTrans.Rollback();
                }
                catch (OleDbException er)
                {
                    if (myTrans.Connection != null)
                    {
                        Debug.WriteLine(er.ToString());
                        //LogErrInfo.WriteErrInfo("数据访问层.txt",er.ToString());;	
                    }
                }

                Debug.WriteLine(e.ToString() + ",异常在：" + strSql);
                throw e;

            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataSet GetOleDataset(string sql)
        {
            OleDbConnection OleConnection = new OleDbConnection(this.szOleConn);
            OleConnection.Open();

            OleDbDataAdapter OleAdapter = new OleDbDataAdapter(sql, this.szOleConn);
            DataSet myDataSet = new DataSet();

            try
            {
                OleConnection.Open();
                OleAdapter.Fill(myDataSet);
                return myDataSet;
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                OleConnection.Close();
            }
        }
        #endregion

        #region 公共的加密接口
        /// <summary>
        /// 密文处理
        /// </summary>
        /// <param name="CodeString">密文</param>
        /// <returns>加密后的字符串</returns>
        public string EncryptCode(string strCode)
        {
            byte[] byCode = ASCIIEncoding.ASCII.GetBytes(strCode);
            byte[] byKey = ASCIIEncoding.ASCII.GetBytes("JrscSoft");
            byte[] byResult = new byte[strCode.Length];

            int j = 0;
            for (int i = 0; i < strCode.Length; i++)
            {
                j = j == byKey.Length ? 1 : j++;
                byResult[i] = (byte)(byCode[i] ^ byKey[j]);
            }

            return ASCIIEncoding.ASCII.GetString(byResult, 0, byResult.Length);
        }
        #endregion

        #region 利用传入的连接串创建数据库连接操作数据库
        /// <summary>
        /// 判断当前连接串是否正确
        /// </summary>
        /// <param name="szOraCon"></param>
        /// <returns></returns>
        public bool CheckOraConnIsRight(string szOraCon)
        {
            bool bRight = false;
            OracleConnection CurOraCon = GetOraConn(szOraCon);
            if (CurOraCon != null)
            {
                try
                {

                    CurOraCon.Open();

                    bRight = true;

                    CurOraCon.Close();
                }
                catch (Exception er)
                {
                    Debug.WriteLine(er.ToString() + ",异常在：" + szOraCon);
                    throw er;
                }
            }
            return bRight;
        }

        /// <summary>
        /// 返回当前数据库连接
        /// </summary>
        /// <returns></returns>
        public OracleConnection GetOraConn(string szOraCon)
        {
            if (szOraCon.Length == 0)
                return null;
            OracleConnection CurOraCon = new OracleConnection(szOraCon);
            try
            {
                if (CurOraCon.State == ConnectionState.Closed)
                {
                    return CurOraCon;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + szOraCon);
                throw er;
            }
        }

        /// <summary>
        /// 执行SQL查询，返回结果的第一行第一列
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns>查询结果</returns>
        public object GetValue(string szOraCon, string sql)
        {
            OracleConnection CurOraCon = this.GetOraConn(szOraCon);

            if (CurOraCon == null)
                return null;

            OracleCommand OraCmd = new OracleCommand(sql, CurOraCon);
            OraCmd.CommandType = CommandType.Text;

            try
            {
                CurOraCon.Open();
                object o = OraCmd.ExecuteScalar();
                CurOraCon.Close();
                return o;
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                CurOraCon.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>					
        public OracleDataReader GetDataReader(string szOraCon, string sql)
        {
            OracleConnection CurOraCon = this.GetOraConn(szOraCon);

            if (CurOraCon == null)
                return null;

            OracleCommand OraCmd = new OracleCommand(sql, CurOraCon);
            OraCmd.CommandType = CommandType.Text;

            try
            {
                CurOraCon.Open();
                return OraCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataSet GetDataset(string szOraCon, string sql)
        {
            OracleConnection CurOraCon = this.GetOraConn(szOraCon);

            if (oraConnection == null)
                return null;

            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, CurOraCon);
            DataSet myDataSet = new DataSet();

            try
            {
                CurOraCon.Open();
                OraAdapter.Fill(myDataSet);
                return myDataSet;
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                CurOraCon.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataTable GetTable(string szOraCon, string sql)
        {
            OracleConnection CurOraCon = this.GetOraConn(szOraCon);

            if (CurOraCon == null)
                return null;

            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, CurOraCon);
            DataSet myDataSet = new DataSet();

            try
            {
                CurOraCon.Open();
                OraAdapter.Fill(myDataSet);
                return myDataSet.Tables[0];
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + sql);
                throw er;
            }
            finally
            {
                CurOraCon.Close();
            }
        }



        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ora连接字符串</param>
        /// <returns>成功即返回真，否则就返回假</returns>
        public bool ExeSql(string szOraCon, string strSql)
        {
            bool bSucceeded = false;
            OracleConnection myConnection = this.GetOraConn(szOraCon);

            if (myConnection == null)
                return bSucceeded;

            myConnection.Open();

            OracleCommand myCommand = myConnection.CreateCommand();
            OracleTransaction myTrans;

            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            myCommand.Transaction = myTrans;

            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                bSucceeded = true;
            }
            catch (Exception er)
            {
                myTrans.Rollback();
                Debug.WriteLine(er.ToString() + ",异常在：" + strSql);
                throw er;
            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }

        /// <summary>
        /// 执行oracle系统级的sql语句
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ora连接字符串</param>
        /// <returns>成功即返回真，否则就返回假</returns>
        public bool ExecSysOraSql(string szOraCon, string strSql)
        {
            bool bSucceeded = false;
            OracleConnection myConnection = this.GetOraConn(szOraCon);

            if (myConnection == null)
                return bSucceeded;

            myConnection.Open();

            OracleCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandType = CommandType.Text;


            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                bSucceeded = true;
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString() + ",异常在：" + strSql);
                throw er;
            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }
        /// <summary>
        /// 执行sql语句
        /// Add By 邱班 at  2009/09/15
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ora连接字符串</param>
        /// <returns>成功即返回真，否则就返回假</returns>
        public bool ExecOraSql(string strSql)
        {
            bool bSucceeded = false;

            OracleConnection myConnection = new OracleConnection(this.szOraConn);
            myConnection.Open();

            OracleCommand myCommand = myConnection.CreateCommand();
            OracleTransaction myTrans;

            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            myCommand.Transaction = myTrans;

            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                bSucceeded = true;
            }
            catch (Exception er)
            {
                myTrans.Rollback();
                Debug.WriteLine(er.ToString() + ",异常在：" + strSql);
                throw er;
            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }

        /// <summary>
        /// 保存数据表到数据库 保存成功返回1，失败返回错误信息
        /// </summary>
        /// <param name="UpdateTable"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public string SaveTable2DB(DataTable UpdateTable, string TableName)
        {
            //open a connection
            OracleConnection conn = this.oraConnection;
            try
            {
                conn.Open();
                //get the data
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = new OracleCommand("select * from " + TableName + " where 1=0", conn);
                OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();
                //newTable.Rows[0].RowState = DataRowState.Added
                adapter.Update(UpdateTable);
                //close the connection
                conn.Close();
                return "1";
            }
            catch (Exception ee)
            {
                return "ERROR:" + ee.Message;
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// 更新指定表数据
        /// Add By 邱班 at  2009/09/15
        /// 添加了事务处理，解决了有部分数据有错误时，只插入部分数据的问题，现在是要么全部成功，要么全部失败。
        /// 2010-5 赵现发 
        /// </summary>
        /// <param name="UpdateTable"></param>
        /// <param name="TableName"></param>
        public bool UpdateTable(DataTable UpdateTable, string TableName)
        {
            //open a connection
            OracleConnection conn = this.oraConnection;

            //get the data
            OracleDataAdapter adapter = new OracleDataAdapter();

            //adapter.SelectCommand = new OracleCommand("select * from " + TableName + " where 1=0", conn);
            OracleCommand myCommand = new OracleCommand("select * from " + TableName + " where 1=0", conn);
            adapter.SelectCommand = myCommand;
            OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
            conn.Open();
            using (OracleTransaction trans = conn.BeginTransaction())
            {
                myCommand.Transaction = trans;
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();

                try
                {
                    adapter.Update(UpdateTable);

                    trans.Commit();

                    conn.Close();
                    return true;
                }
                catch (Exception ee)
                {
                    UpdateTable.RejectChanges();
                    trans.Rollback();
                    throw ee;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 更新表数据,其他错误则抛出 （通用）
        /// </summary>
        /// 2010-5 赵现发
        /// <param name="UpdateTable"></param>
        /// <param name="TableName">表名称</param>
        /// <param name="IsIgnoreKeyConflict">是否跳过主键冲突</param>
        /// <returns></returns>
        public bool UpdateTable2Key(DataTable UpdateTable, string TableName, bool IsIgnoreKeyConflict)
        {
            this.IsIgnoreKeyConflict = IsIgnoreKeyConflict;
            //open a connection
            OracleConnection conn = this.oraConnection;

            //get the data
            OracleDataAdapter adapter = new OracleDataAdapter();

            OracleCommand myCommand = new OracleCommand("select * from " + TableName + " where 1=0", conn);
            adapter.SelectCommand = myCommand;
            OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
            conn.Open();
            using (OracleTransaction trans = conn.BeginTransaction())
            {
                myCommand.Transaction = trans;

                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();

                // add handlers 添加行绑定事件
                adapter.RowUpdating += new OracleRowUpdatingEventHandler(OnRowUpdating);
                adapter.RowUpdated += new OracleRowUpdatedEventHandler(OnRowUpdated);

                adapter.Update(UpdateTable);

                // remove handlers 删除行绑定事件
                adapter.RowUpdating -= new OracleRowUpdatingEventHandler(OnRowUpdating);
                adapter.RowUpdated -= new OracleRowUpdatedEventHandler(OnRowUpdated);

                foreach (DataRow row in UpdateTable.Rows)
                {
                    if (row.HasErrors)
                    {
                        //WriteErrorLog(row.RowError);
                        UpdateTable.RejectChanges();
                        trans.Rollback();
                        conn.Close();
                        throw new Exception(row.RowError);
                        //return false ;
                    }
                }
                trans.Commit();
                conn.Close();
                return true;
            }
        }
        public bool UpdateTable2Key(DataTable UpdateTable, string TableName)
        {
            //open a connection
            OracleConnection conn = this.oraConnection;

            //get the data
            OracleDataAdapter adapter = new OracleDataAdapter();

            OracleCommand myCommand = new OracleCommand("select * from " + TableName + " where 1=0", conn);
            adapter.SelectCommand = myCommand;
            OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
            conn.Open();
            using (OracleTransaction trans = conn.BeginTransaction())
            {
                myCommand.Transaction = trans;

                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();

                // add handlers 添加行绑定事件
                adapter.RowUpdating += new OracleRowUpdatingEventHandler(OnRowUpdating);
                adapter.RowUpdated += new OracleRowUpdatedEventHandler(OnRowUpdated);

                adapter.Update(UpdateTable);

                // remove handlers 删除行绑定事件
                adapter.RowUpdating -= new OracleRowUpdatingEventHandler(OnRowUpdating);
                adapter.RowUpdated -= new OracleRowUpdatedEventHandler(OnRowUpdated);

                foreach (DataRow row in UpdateTable.Rows)
                {
                    if (row.HasErrors)
                    {
                        //WriteErrorLog(row.RowError);
                        UpdateTable.RejectChanges();
                        trans.Rollback();
                        conn.Close();
                        throw new Exception(row.RowError);
                        //return false ;
                    }
                }
                trans.Commit();
                conn.Close();
                return true;
            }
        }
        /// <summary>
        /// 行更新前事件
        /// 2010-5 赵现发
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="args"></param>
        private void OnRowUpdating(object sender, OracleRowUpdatingEventArgs args)
        {
            iCurRowsNum = iCurRowsNum + 1;
            //if (args.StatementType == StatementType.Insert)
            //{
            //    System.IO.TextWriter writer = System.IO.File.AppendText("e:\\OnRowUpdating.log");
            //    writer.WriteLine("{0}: 错误信息： {1} .",
            //        DateTime.Now, args.Row[0]);
            //    writer.Close();
            //}
        }
        /// <summary>
        /// 行更新后事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="args"></param>
        private void OnRowUpdated(object sender, OracleRowUpdatedEventArgs args)
        {
            if (args.Status == UpdateStatus.ErrorsOccurred)
            {
                string sErrorNo = args.Errors.Message.Substring(0, 10).ToString();
                if (sErrorNo == "ORA-00001:")
                {
                    if (!this.IsIgnoreKeyConflict)
                    {
                        throw new Exception("错误行数：" + iCurRowsNum.ToString() + " 行。" + args.Errors.Message);
                    }
                    //args.Row.RowError = args.Errors.Message;
                    //WriteErrorLog("第" + args.Row.Table.Rows.Count.ToString() + "行 错误信息：" + args.Errors.Message);

                    args.Status = UpdateStatus.SkipCurrentRow;
                }
                else
                {
                    throw new Exception("错误行数：" + iCurRowsNum.ToString() + " 行。" + args.Errors.Message);
                }
            }
        }

        /// <summary>
        /// 更新指定表数据
        /// Add By 邱班 at  2009/09/15
        /// </summary>
        /// <param name="UpdateTable"></param>
        /// <param name="UpdateTableSql"></param>
        public bool UpdateTableBySql(DataTable UpdateTable, string UpdateTableSql)
        {
            //open a connection
            OracleConnection conn = this.oraConnection;
            using (OracleTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    conn.Open();
                    //get the data
                    OracleDataAdapter adapter = new OracleDataAdapter();
                    adapter.SelectCommand = new OracleCommand(UpdateTableSql, conn);

                    OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
                    adapter.UpdateCommand = builder.GetUpdateCommand();
                    adapter.InsertCommand = builder.GetInsertCommand();
                    adapter.DeleteCommand = builder.GetDeleteCommand();
                    //newTable.Rows[0].RowState = DataRowState.Added

                    adapter.UpdateCommand.Transaction = trans;
                    adapter.InsertCommand.Transaction = trans;
                    adapter.DeleteCommand.Transaction = trans;

                    adapter.Update(UpdateTable);

                    trans.Commit();
                    //close the connection
                    conn.Close();
                    return true;
                }
                catch (Exception ee)
                {
                    trans.Rollback();
                    throw ee;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 更新指定表指定字段数据
        /// Add By 邱班 at  2009/09/15
        /// </summary>
        /// <param name="UpdateTable"></param>
        /// <param name="UpdateField"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>        
        public bool UpdateTable(DataTable UpdateTable, string UpdateField, string TableName)
        {
            //open a connection
            OracleConnection conn = this.oraConnection;
            try
            {
                conn.Open();
                //get the data
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = new OracleCommand("select  " + UpdateField + "  from " + TableName + " where 1=0", conn);
                OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();
                //newTable.Rows[0].RowState = DataRowState.Added
                adapter.Update(UpdateTable);
                //close the connection
                conn.Close();
                return true;
            }
            catch (Exception ee)
            {
                throw ee;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 更新指定表指定字段数据
        /// 本函数和上面的UpdateTable完全一样，只是保存成功时返回1，失败时返回错误信息  20100127张玉峰
        /// </summary>
        /// <param name="UpdateTable"></param>
        /// <param name="UpdateField"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public string UpdateTableWithMessage(DataTable UpdateTable, string UpdateField, string TableName)
        {
            //open a connection
            OracleConnection conn = this.oraConnection;
            try
            {
                conn.Open();
                //get the data
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = new OracleCommand("select  " + UpdateField + "  from " + TableName + " where 1=0", conn);
                OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();
                //newTable.Rows[0].RowState = DataRowState.Added
                adapter.Update(UpdateTable);
                //close the connection
                conn.Close();
                return "1";
            }
            catch (Exception ee)
            {
                //return "ERROR:" + ee.Message;

                throw ee;
            }
            finally
            {
                conn.Close();
            }
        }

        

        #endregion

        #region 调用存储过程----------------------------------------------------------


        /// <summary>
        /// 重载Fill（应用存储过程填充）
        /// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
        /// </summary>
        /// <param name="str_procedure_name">存储过程的名字</param>
        /// <param name="str_Param">参数列表0-name of paramaeter ,1-value of parameter,2-input or output of parameter</param>

        public DataSet Fill(string str_procedure_Name, ArrayList[] Arr_Param, OracleConnection p_Connection)
        {
            #region 代码实现
            OracleConnection CurSqlCon = p_Connection;

            if (CurSqlCon == null)
                return null;

            OracleCommand myCommand = new OracleCommand();

            myCommand.Connection = CurSqlCon;
            //myAdapter.SelectCommand  = new OracleCommand("SqlmyCommend", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = str_procedure_Name;
            if ((Arr_Param != null) && (Arr_Param.Length > 0))
            {
                OracleParameter[] myParameter = new OracleParameter[Arr_Param[0].Count];
                for (int i = 0; i < Arr_Param[0].Count; i++)
                {
                    if ((object)Arr_Param[2] == null || Arr_Param[2][i].ToString().ToUpper() == "INPUT")
                    {
                        string d = Arr_Param[0][i].ToString();
                        myParameter[i] = new OracleParameter(Arr_Param[0][i].ToString(), (object)Arr_Param[1][i]);


                        myCommand.Parameters.Add(myParameter[i]);

                    }
                    else
                    {
                        myParameter[i] = new OracleParameter(Arr_Param[0][i].ToString(), OracleType.Cursor);
                        myParameter[i].Direction = ParameterDirection.Output;
                        myCommand.Parameters.Add(myParameter[i]);

                    }
                }
            }

            OracleDataAdapter myAdapter = new OracleDataAdapter();
            myAdapter.SelectCommand = myCommand;
            myAdapter.SelectCommand.CommandTimeout = 36000;  //查询的超时时间
            DataSet ds = new DataSet();

            try
            {
                CurSqlCon.Open();
                myAdapter.Fill(ds);

            }
            catch (Exception ee)
            {
                string dd = ee.Message;
                throw ee;
            }
            finally
            {
                CurSqlCon.Close();
            }
            return ds;

            #endregion 代码实现
        }

        public DataSet Fill(string str_procedure_Name, ArrayList[] Arr_Param)
        {
            return Fill(str_procedure_Name, Arr_Param, oraConnection);
        }
        /// <summary>
        /// 重载Exe_Pro_Fun（执行存储过程）
        /// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
        /// </summary>
        /// <param name="str_procedure_name">存储过程的名字</param>
        /// <param name="str_Param_name">参数列表0-name of paramaeter ,1-value of parameter,2-input or output of parameter</param>
        ///[Ajax.AjaxMethod()]
        public void Exe_Pro_Fun(string str_procedure_Name, ArrayList[] Arr_Param, OracleConnection p_Connection)
        {
            #region 代码实现

            OracleConnection CurSqlCon = p_Connection;

            if (CurSqlCon == null)
                return;
            OracleCommand myCommand = new OracleCommand();

            myCommand.Connection = CurSqlCon;
            //myAdapter.SelectCommand  = new OracleCommand("SqlmyCommend", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = str_procedure_Name;
            if ((Arr_Param != null) && (Arr_Param.Length > 0))
            {
                OracleParameter[] myParameter = new OracleParameter[Arr_Param[0].Count];
                for (int i = 0; i < Arr_Param[0].Count; i++)
                {
                    try
                    {
                        if ((object)Arr_Param[2] == null || Arr_Param[2][i].ToString().ToUpper() == "INPUT")
                        {
                            myParameter[i] = new OracleParameter(Arr_Param[0][i].ToString(), (object)Arr_Param[1][i]);

                            myCommand.Parameters.Add(myParameter[i]);
                        }
                        else
                        {
                            return;
                        }
                    }

                    catch (Exception ee)
                    {
                        string d = ee.Message; ;
                    }
                }
            }
            //MyAdapter.SelectCommand.CommandTimeout=36000;  //查询的超时时间

            try
            {
                CurSqlCon.Open();
                myCommand.CommandTimeout = 360000;
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ee)
            {
                // System.Diagnostics.Trace.Write(ee.Message);
                throw ee;
            }
            finally
            {
                CurSqlCon.Close();
            }

            #endregion 代码实现
        }

        public void Exe_Pro_Fun(string str_procedure_Name, ArrayList[] Arr_Param)
        {
            Exe_Pro_Fun(str_procedure_Name, Arr_Param, oraConnection);
        }

        /// <summary>
        /// 重载Exe_Pro_Fun（执行存储过程）
        /// 建立DataSet对象,用记录填充或构架(如果必要)DataSet对象,DataSet即是数据在内存的缓存
        /// </summary>
        /// <param name="str_procedure_name">存储过程的名字</param>
        /// <param name="str_Param_name">参数列表0-name of paramaeter ,1-value of parameter,2-input or output of parameter</param>
        ///[Ajax.AjaxMethod()]
        public string Return_Exe_Pro_Fun(string str_procedure_Name, ArrayList[] Arr_Param, OracleConnection p_Connection)
        {
            #region 代码实现

            OracleConnection CurSqlCon = p_Connection;

            if (CurSqlCon == null)
                return "连接异常，提交失败!";
            OracleCommand myCommand = new OracleCommand();

            myCommand.Connection = CurSqlCon;
            //myAdapter.SelectCommand  = new OracleCommand("SqlmyCommend", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = str_procedure_Name;
            if ((Arr_Param != null) && (Arr_Param.Length > 0))
            {
                OracleParameter[] myParameter = new OracleParameter[Arr_Param[0].Count];
                for (int i = 0; i < Arr_Param[0].Count; i++)
                {
                    try
                    {

                        myParameter[i] = new OracleParameter(Arr_Param[0][i].ToString(), (object)Arr_Param[1][i]);

                        myCommand.Parameters.Add(myParameter[i]);
                        if ((object)Arr_Param[2] != null && Arr_Param[2][i].ToString().ToUpper() != "INPUT")
                        {
                            myCommand.Parameters[i].Direction = ParameterDirection.Output;
                        }
                    }

                    catch (Exception ee)
                    {
                        return "参数异常，数据提交失败!";
                    }
                }
            }
            //MyAdapter.SelectCommand.CommandTimeout=36000;  //查询的超时时间

            try
            {
                CurSqlCon.Open();
                myCommand.CommandTimeout = 360000;
                myCommand.ExecuteNonQuery();
                if ((object)myCommand.Parameters[Arr_Param[0].Count - 1] != null && (object)myCommand.Parameters[Arr_Param[0].Count - 1] != System.DBNull.Value && myCommand.Parameters[Arr_Param[0].Count - 1].Value.ToString() != "0" && myCommand.Parameters[Arr_Param[0].Count - 1].Value.ToString() != "")
                    return myCommand.Parameters[Arr_Param[0].Count - 1].Value.ToString();
                else
                    return "数据提交成功！";

            }
            catch (Exception ee)
            {
                //return "存储过程异常，数据提交失败!";
                throw ee;
            }
            finally
            {
                CurSqlCon.Close();
            }

            #endregion 代码实现
        }

        public string Return_Exe_Pro_Fun(string str_procedure_Name, ArrayList[] Arr_Param)
        {
            return Return_Exe_Pro_Fun(str_procedure_Name, Arr_Param, oraConnection);
        }

        /// <summary>
        /// 
        /// 根据指定的查询条件，从数据库中获得指定数量的记录
        /// </summary>
        /// <param name="p_Index">要显示第几页的数据</param>
        /// <param name="p_Sql">查询语句</param>
        /// <param name="p_Size">每页显示的记录数量</param>
        /// <param name="p_PAGECOUNT">传出参数：取得页总数</param>
        /// <param name="p_ROWSCOUNT">传出参数：取得本次查询出的记录总数</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTableFromProcedure(
            int p_Index,
            string p_Sql,
            int p_Size,
            out int p_PAGECOUNT,
            out int p_ROWSCOUNT)
        {
            #region 根据指定的查询条件，从数据库中获得指定数量的记录到DataTable

            int iIndex, iSize;
            string strSql;
            iIndex = p_Index; iSize = p_Size; strSql = p_Sql;

            OracleConnection conn = this.oraConnection;
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "JrscSoft.Page";

            cmd.Parameters.Add("PINDEX", OracleType.Number).Value = iIndex;
            cmd.Parameters.Add("PSQL", OracleType.VarChar).Value = strSql;
            cmd.Parameters.Add("PSIZE", OracleType.Number).Value = iSize;
            cmd.Parameters.Add("PROWS", OracleType.Number).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("PCOUNT", OracleType.Number).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("V_CUR", OracleType.Cursor).Direction = ParameterDirection.Output;

            cmd.Connection = conn;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            DataTable dt = new DataTable();
            try
            {
                OracleDataAdapter adp = new OracleDataAdapter();
                adp.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adp.Fill(ds);
                dt = ds.Tables[0];
                //				ViewState["PAGECOUNT"] = cmd.Parameters["PCOUNT"].Value;  //取得总页数
                //				ViewState["ROWSCOUNT"] = cmd.Parameters["PROWS"].Value;  //取得记录总数
                p_PAGECOUNT = Convert.ToInt32(cmd.Parameters["PCOUNT"].Value);  //取得总页数
                p_ROWSCOUNT = Convert.ToInt32(cmd.Parameters["PROWS"].Value);  //取得记录总数
            }
            catch (Exception ee)
            {
                throw new Exception("错误发生在：" + p_Sql + "   " + ee.Message);
            }
            finally
            {
                conn.Close();
            }

            return dt;

            #endregion 根据指定的查询条件，从数据库中获得指定数量的记录到DataTable

            //private DataTable GetDataTableFromProcedure(string p_Sql)
        }

        /// <summary>
        /// 把错误信息写到c盘更目录下的Error.log中
        /// </summary>
        /// 赵现发
        /// <param name="ErrorInfo">错误信息</param>
        public static void WriteErrorLog(string ErrorInfo)
        {
            System.IO.TextWriter writer = System.IO.File.AppendText("c:\\Error.log");
            writer.WriteLine("{0}: 错误信息： {1} .",
                DateTime.Now, ErrorInfo);
            writer.Close();
        }

        #endregion 调用存储过程

        internal bool UpdateTable(DataTable UpdateTableCopy, DataTable UpdateTable, string TableName)
        {
            throw new NotImplementedException();
        }
    }
}
